---
description: >
  Manage PostgreSQL databases from the terminal.
title: PG Client Applications
---

<details markdown="1" id="table-of-contents">
<summary>
Table of Contents
</summary>

* TOC
{:toc}
</details>


## psql

Start `psql` connected to \<db> as \<user>.

``` terminal
$ psql -U <user> -d <db>
```

<div markdown="1" class="table">

| description              | command         |
|--------------------------|-----------------|
| quit session             | `\q` or `\!`    |
| help                     | `\?`            |
| help with \<sql>         | `\h <sql>`      |
| list all databases       | `\l`            |
| defined users            | `\du`           |
| defined tables           | `\dt`           |
| defined schemas          | `\dn`           |
| defined functions        | `\df`           |
| defined views            | `\dv`           |
| \<relation>* definition  | `\d <relation>` |
| show history             | `\s`            |
| save history to \<file>  | `\s <file>`     |
| show all commands syntax | `\h *`          |
| connect/change to \<db>  | `\connect <db>` |

</div>

\* Where \<relation> can be a table, index, view, or sequence.

## CLI

Create \<admin> user/role using \<superuser>.

```terminal
$ createuser -U <superuser> -P --createdb --createrole <admin>
```

_Note_: \<admin> should own the \<admin> db.

Create \<db> for \<owner> using \<admin>.

```terminal
$ createdb -U <admin> -O <owner> <db>
```

Use \<admin> to create \<new\_user>. The `-P` flag will prompt for \<new_user>'s password.

```terminal
$ createuser -P -U <admin> <new_user>
```

Remove \<user> using \<admin>.

```terminal
$ dropuser -U <admin> <user>
```

Remove \<db> using \<admin>.

```terminal
$ dropdb -U <admin> <db>
```

Extract a database into a script file or other archive.

```terminal
$ pg_dump -U <user> <db> > <output>.sql
```

Restore an archive created with `pg_dump`.

```terminal
$ pg_restore -j <jobs> -U <user> -d <db> <dump>
```

## Resources

- [Psql section](https://momjian.us/main/writings/pgsql/aw_pgsql_book/node142.html){:rel="nofollow noreferrer noopener"}
from [PostgreSQL: Introduction & Concepts.](https://momjian.us/main/writings/pgsql/aw_pgsql_book/0.html){:rel="nofollow noreferrer noopener"}
- [Psql Manual](https://www.postgresql.org/docs/current/static/app-psql.html){:rel="nofollow noreferrer noopener"}
- [PostgreSQL Client Applications](https://www.postgresql.org/docs/current/static/reference-client.html){:rel="nofollow noreferrer noopener"}
